﻿using System;
using System.Windows.Forms;
using System.Xml;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;
using System.Xml;
using Ehost;
using System.IO;

namespace GetTestScriptEHost
{
    public partial class Form1 : Form
    {
        XmlDocument Xdoc;
        public Form1()
        {
            InitializeComponent();
        }


        private void txtOpenFile_MouseClick(object sender, MouseEventArgs e)
        {
            OpenFileDialog fdialog = new OpenFileDialog();
            fdialog.Title = "Open XML Testsuite Files";
            fdialog.Filter = "XML File|*.xml";
            fdialog.InitialDirectory = @"C:\";
            fdialog.ShowDialog();

            txtOpenFile.Text = fdialog.FileName;
            lstTestSuite.Items.Clear();
        }

        private void btnAnalyseXML_Click(object sender, EventArgs e)
        {
            if (txtOpenFile.Text != null)
            {
                if (txtOpenFile.Text != "Click to open")
                {

                    Xdoc = new XmlDocument();
                    //Xdoc.LoadXml(txtOpenFile.Text.ToString());
                    Xdoc.Load(txtOpenFile.Text);

                    XmlNodeList nodelist = Xdoc.SelectNodes("//testsuites/testsuite");
                    lstTestSuite.Items.Clear();
                    foreach (XmlNode node in nodelist)
                    {
                        lstTestSuite.Items.Add(node.Attributes["name"].Value);
                        lstTestSuite.Items.Remove("Regression");
                        lstTestSuite.Items.Remove("SpotCheck");
                    }
                }
                else
                {
                    MessageBox.Show("Please select a xml file", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            else
            {
                MessageBox.Show("Please select a xml file", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void btnGenExcel_Click(object sender, EventArgs e)
        {
            if (txtOpenFile.Text != null)
            {
                if (txtOpenFile.Text != "Click to open")
                {
                    if (lstTestSuite.Items.Count != 0)
                    {
                        if (lstTestSuite.SelectedItem != null)
                        {
                            btnGenExcel.Text = "Generating...";
                            btnGenExcel.Enabled = false;
                            btnAnalyseXML.Enabled = false;
                            foreach (Object selecteditem in lstTestSuite.SelectedItems)
                            {
                                try
                                {

                                    //Get title and set output of script                
                                    //string output = @"C:\" + lstTestSuite.SelectedItem.ToString();
                                    //string title = lstTestSuite.SelectedItem.ToString();

                                    string title = selecteditem.ToString();
                                    //string output = @"C:\" + selecteditem.ToString();

                                    string output = selecteditem.ToString();

                                    string filePath = convertToExcel(title, output);
                                }
                                catch (Exception ex)
                                {
                                    MessageBox.Show("Cannot converto xml file to excel file: " + ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                }
                            }
                            btnGenExcel.Text = "Generate";
                            btnGenExcel.Enabled = true;
                            btnAnalyseXML.Enabled = true;
                        }
                        else
                        {
                            MessageBox.Show("Please select at least 01 script in listbox", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                    }
                    else
                    {
                        MessageBox.Show("Please click \"Analyse XML\" button", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                }
                else
                {
                    MessageBox.Show("Please select a xml file", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            else
            {
                MessageBox.Show("Please select a xml file", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }


        private void setColumnWidth(Excel.Worksheet xlWorkSheet)
        {
            ((Excel.Range)xlWorkSheet.Columns["A"]).ColumnWidth = 10;
            ((Excel.Range)xlWorkSheet.Columns["B"]).ColumnWidth = 20;
            ((Excel.Range)xlWorkSheet.Columns["C"]).ColumnWidth = 40;
            ((Excel.Range)xlWorkSheet.Columns["D"]).ColumnWidth = 30;
            ((Excel.Range)xlWorkSheet.Columns["E"]).ColumnWidth = 40;
            ((Excel.Range)xlWorkSheet.Columns["F"]).ColumnWidth = 40;
            ((Excel.Range)xlWorkSheet.Columns["G"]).ColumnWidth = 10;
            ((Excel.Range)xlWorkSheet.Columns["H"]).ColumnWidth = 20;
            ((Excel.Range)xlWorkSheet.Columns["I"]).ColumnWidth = 20;
            ((Excel.Range)xlWorkSheet.Columns["J"]).ColumnWidth = 20;
            ((Excel.Range)xlWorkSheet.Columns["k"]).ColumnWidth = 30;
            // Add applicable interfaces
            ((Excel.Range)xlWorkSheet.Columns["L"]).ColumnWidth = 30;
        }

        private void formatCell(Excel.Worksheet xlWorkSheet, int index)
        {

            #region Alignment for cell
            xlWorkSheet.get_Range("A" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet.get_Range("A" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            xlWorkSheet.get_Range("B" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("B" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("C" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("C" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("D" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("D" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("E" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("E" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("F" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("F" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("G" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("G" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("H" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("H" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("I" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("I" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("J" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("J" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("K" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("K" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            // Add applicable interfaces
            xlWorkSheet.get_Range("L" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("L" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            #endregion

            #region Wrap text for cell
            xlWorkSheet.Range["A" + index].WrapText = true;
            xlWorkSheet.Range["B" + index].WrapText = true;
            xlWorkSheet.Range["C" + index].WrapText = true;
            xlWorkSheet.Range["D" + index].WrapText = true;
            xlWorkSheet.Range["E" + index].WrapText = true;
            xlWorkSheet.Range["F" + index].WrapText = true;
            xlWorkSheet.Range["G" + index].WrapText = true;
            xlWorkSheet.Range["H" + index].WrapText = true;
            xlWorkSheet.Range["I" + index].WrapText = true;
            xlWorkSheet.Range["J" + index].WrapText = true;
            xlWorkSheet.Range["K" + index].WrapText = true;
            // Add applicable interfaces
            xlWorkSheet.Range["L" + index].WrapText = true;
            #endregion

            #region Set border for cell
            xlWorkSheet.Range["A" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["B" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["C" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["D" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["E" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["F" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["G" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["H" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["I" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["J" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["K" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            // Add applicable interfaces
            xlWorkSheet.Range["L" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            #endregion


        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        public string convertToExcel(string titlescript, string output)
        {
            string filePath;
            string steps;
            string testSuiteName;
            string testScript;
            try
            {
                XmlHelper xml = new XmlHelper();
                xml.parseXml(txtOpenFile.Text);
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                Excel.Range chartRange;
                int rowStart = 1;
                xlApp = new Excel.ApplicationClass();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                xlWorkSheet.Cells[rowStart, 1] = "#";
                xlWorkSheet.Cells[rowStart, 2] = "TestLink ID";
                xlWorkSheet.Cells[rowStart, 3] = "Summary";
                xlWorkSheet.Cells[rowStart, 4] = "Applicable Interfaces";
                xlWorkSheet.Cells[rowStart, 5] = "Preconditions";
                xlWorkSheet.Cells[rowStart, 6] = "Actions";
                xlWorkSheet.Cells[rowStart, 7] = "Expected Result";
                xlWorkSheet.Cells[rowStart, 8] = "Priority";
                xlWorkSheet.Cells[rowStart, 9] = "Notes";
                xlWorkSheet.Cells[rowStart, 10] = "Jira Requirement ID";
                xlWorkSheet.Cells[rowStart, 11] = "Automated";
                xlWorkSheet.Cells[rowStart, 12] = "Test script";
                
                xlWorkSheet.get_Range("A1", "L1").Interior.Color = 65535;
                xlWorkSheet.get_Range("A1", "L1").Font.Bold = true;
                setColumnWidth(xlWorkSheet);
                xlWorkSheet.Name = "Test case";

                formatCell(xlWorkSheet, rowStart);

                // Get node script
                //XmlNodeList nodeList = xml.getNodeListByXpath("//testsuites/testsuite");
                XmlNodeList testSuiteList = Xdoc.SelectNodes("//testsuite[@name='" + titlescript + "']");

                int index = 2;
                int indexRow = 1;

                //for (int i = 0; i < nodeList.Count; i++)
                //{
                //testSuiteName = nodeList[i].Attributes["name"].Value;
                testSuiteName = titlescript;

                xlWorkSheet.Cells[index, 1] = testSuiteName;
                chartRange = xlWorkSheet.get_Range("A" + index, "L" + index);
                chartRange.Merge(false);
                chartRange.Interior.Color = 52613;
                chartRange.Font.Bold = true;
                formatCell(xlWorkSheet, index);
                index = index + 1;
                //XmlNodeList testSuiteList = xml.getNodeList(nodeList[i], "testsuite/testsuite");
                //for (int j = 0; j < testSuiteList.Count; j++)
                //{

                XmlNodeList testCaseList = xml.getNodeList(testSuiteList[0], "//testsuite/testcase");
                //testScript = testSuiteList[j].Attributes["name"].Value;
                testScript = titlescript;
                for (int k = 0; k < testCaseList.Count; k++)
                {
                    xlWorkSheet.Cells[index, 1] = indexRow;
                    xlWorkSheet.Cells[index, 2] = "EHTC-" + xml.getTestLinkID(testCaseList[k]);

                    xlWorkSheet.Cells[index, 3] = xml.getSummary(testCaseList[k]);
                    // Add applicable interfaces
                    xlWorkSheet.Cells[index, 4] = xml.getApplicableInterfaces(testCaseList[k]);
                    // End Add applicable interfaces
                    xlWorkSheet.Cells[index, 5] = xml.getPreconditions(testCaseList[k]);

                    xml.getSteps(testCaseList[k]);
                    steps = string.Empty;
                    string expected = string.Empty;
                    for (int l = 0; l < xml.lstSteps.Count; l++)
                    {
                        steps = steps == string.Empty ? steps + xml.lstSteps[l].ToString() : steps + "\n" + xml.lstSteps[l].ToString();
                        expected = expected == string.Empty ? expected + xml.lstExpected[l].ToString() : expected + "\n" + xml.lstExpected[l].ToString();
                    }
                    xlWorkSheet.Cells[index, 6] = steps;
                    xlWorkSheet.Cells[index, 7] = expected;
                    xlWorkSheet.Cells[index, 8] = xml.getImportant(testCaseList[k]); ;
                    xlWorkSheet.Cells[index, 9] = xml.getNotes(testCaseList[k]);
                    xlWorkSheet.Cells[index, 10] = xml.getJira(testCaseList[k]);
                    xlWorkSheet.Cells[index, 11] = xml.getAutomationStatus(testCaseList[k]);
                    xlWorkSheet.Cells[index, 12] = testScript;
                    
                    formatCell(xlWorkSheet, index);
                    index = index + 1;
                    indexRow = indexRow + 1;
                }
                //}             


                xlWorkSheet.get_Range("A1", "L1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                xlWorkSheet.get_Range("A1", "L1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

                // Trim the invalid filename of excel file
                output = trimInvalidFilename(output);
              
                filePath = @"C:\" + output + "_Ehost_Testcase.xlsx";
                xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlApp);
                releaseObject(xlWorkBook);
                releaseObject(xlWorkSheet);

            }
            catch (Exception ex)
            {
                throw;
            }

            return filePath;

        }

        public string trimInvalidFilename(string filename) 
        {
            string regexSearch = new string(Path.GetInvalidFileNameChars()) + new string(Path.GetInvalidPathChars());
            Regex r = new Regex(string.Format("[{0}]", Regex.Escape(regexSearch)));
            filename = r.Replace(filename, "");

            return filename;
        }
    }
}
